package com.sj.stone.domain.oracle;

import com.sj.stone.domain.oracle.entry.Order;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;

import java.util.List;

public interface OrderRepository extends JpaRepository<Order,String> {

    Order findAllByOrderNum(String orderNum);
    //查询客户未付款订单
    @Query(value = "select s.ORDER_NO from SJ_BUZMGT.BIZ_ORDER_SIGNFOR s\n" +
            "    left join  SJ_BUZMGT.biz_unpayment_remark r\n" +
            "    on s.ORDER_NO = r.ORDERNO\n" +
            "    where s.MEMBER_PHONE = ?1 \n" +
            "    and ORDER_STATUS != 4\n" +
            "    and ORDER_STATUS != 1\n" +
            "    and (ORDER_PAY_TYPE != 0 or ORDER_PAY_TYPE is null)\n" +
            "    and YEWU_SIGNFOR_TIME is not null\n" +
            "    and BUSH_TIME is null\n" +
            "    and r.ID is null\n" +
            "    and s.CREAT_TIME  >=  to_date('2017-02-20 00:00','yyyy-mm-dd hh24:mi')\n" +
            "    and s.YEWU_SIGNFOR_TIME  <=  to_date('2017-12-30 00:00','yyyy-mm-dd hh24:mi')", nativeQuery = true)
    List lookOrderNo(String phone);
}
